package kr.hkit.score_pjt.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.framework.dao.CloseDaoUtil;
import kr.hkit.framework.dao.LoadPropertyDAO;
import kr.hkit.score_pjt.DTO.StdInfo;

public class StdInfoDao {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;

	public StdInfoDao() {
	}

	public StdInfoDao(Connection con) {
		this.con = con;
	}

	public void setCon(Connection con) {
		this.con = con;
	}

	// insert into stdinfo values(100, '이순신', 85, 30, 100);
	public void insertProduct(StdInfo dto) throws SQLException {
		String sql = "insert into stdinfo values(?,?,?,?,?)";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, dto.getStdNum());
		pstmt.setString(2, dto.getStdName());
		pstmt.setInt(3, dto.getKorScore());
		pstmt.setInt(4, dto.getEngScore());
		pstmt.setInt(5, dto.getMathScore());

		// System.out.println(pstmt);
		int row = pstmt.executeUpdate();
		CloseDaoUtil.close(pstmt);
	}

	public ArrayList<StdInfo> selectAllStdInfoList() throws Exception {
		String sql = "select grade, rank, stdnum, stdname, kor, eng, math, total, avg from view_stdinfo";
		ArrayList<StdInfo> lists = new ArrayList<>();
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		StdInfo std = null;
		while (rs.next()) {
			std = new StdInfo();
			std.setStdGrade(rs.getInt("grade")).setStdRank(rs.getInt("rank"));
			std.setStdNum(rs.getInt("stdnum")).setStdName(
					rs.getString("stdName"));
			std.setKorScore(rs.getInt("kor")).setEngScore(rs.getInt("eng"))
					.setMathScore(rs.getInt("math"));
			std.setTotal(rs.getInt("total")).setAvg(rs.getInt("avg"));
			lists.add(std);
		}
		if(lists.size() == 0){
			throw new Exception("학생정보가 없음");
		}
		return lists;
	}

	public void deleteAllStdInfo() throws SQLException {
		String sql = "delete from stdinfo";
		pstmt = con.prepareStatement(sql);
		pstmt.executeUpdate();
		CloseDaoUtil.close(pstmt);
	}

	public String selectStdNameByStdnum(int stdNum) throws Exception{
		String sql = "select stdname from stdcode where stdnum=?;";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, stdNum);
		rs = pstmt.executeQuery();
		if(rs.next()){
			return rs.getString(1);
		} else {
			throw new Exception("해당학생이 존재하지 않음");
		}
	
	}
	
	/*
	 * public static void main(String[] args) throws SQLException { StdInfoDao
	 * dao = new StdInfoDao(LoadPropertyDAO.getConnection());
	 * System.out.println(dao.selectAllStdInfoList()); }
	 */
}
